﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Compta;
using System.Configuration;
using System.Data.OleDb;
using DAO;

namespace DAO.Compta
{
    public class ComptaAccountsDAO
    {
        #region "Accounts"
        public static List<Account> getAllAccounts()
        {
            List<Account> _return = new List<Account>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT * from ACCOUNTS", conn);
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;
            string accountNumber, bank;
            double sold;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                accountNumber = (string)myOleDbDataReader.GetValue(1);
                bank = (string)myOleDbDataReader.GetValue(2);
                sold = (double)myOleDbDataReader.GetValue(3);
                _return.Add(new Account(id, accountNumber, bank, sold));

            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static List<Account> getAllAccounts(int idUser)
        {
            List<Account> _return = new List<Account>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT acct.* from ACCOUNTS acct, USERS_ACCOUNT uAcct where uAcct.ID_USER = @idUser and uAcct.ID_ACCOUNT = acct.ID", conn);
            myOleDbCommand2.Parameters.Add("@idUser", OleDbType.Integer).Value = idUser;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;
            string accountNumber, bank;
            double sold;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                accountNumber = (string)myOleDbDataReader.GetValue(1);
                bank = (string)myOleDbDataReader.GetValue(2);
                sold = (double)myOleDbDataReader.GetValue(3);
                _return.Add(new Account(id, accountNumber, bank, sold));

            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }


        public static List<int> getUsersForAccount(int idAcct)
        {
            List<int> _return = new List<int>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT ID_USER from USERS_ACCOUNT where ID_ACCOUNT = @idAccount", conn);
            myOleDbCommand2.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAcct;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                _return.Add(id);

            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static int deleteUserForAccount(int idAcct, int idUser)
        {
            int _return = -1;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from USERS_ACCOUNT where ID_USER = @idUser and ID_ACCOUNT = @idAccount";
            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = idUser;
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAcct;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }

        public static void insertUserForAccount(int idAcct, int idUser)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "insert into USERS_ACCOUNT(ID_USER, ID_ACCOUNT) values(@idUser,@idAccount)";
            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = idUser;
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAcct;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static void ModifyAccount(Account line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update ACCOUNTS set ACCOUNT_NUMBER = @accountNumber, BANK = @bank, SOLD = @sold where ID = @id";
            myCommand.Parameters.Add("@accountNumber", OleDbType.VarChar).Value = line.AccountNumber;
            myCommand.Parameters.Add("@bank", OleDbType.VarChar).Value = line.Bank;
            myCommand.Parameters.Add("@sold", OleDbType.VarChar).Value = line.Sold;
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewAccount(Account line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into ACCOUNTS(ACCOUNT_NUMBER, BANK, SOLD) VALUES (@accountNumber, @bank, @sold)";
            myCommand.Parameters.Add("@accountNumber", OleDbType.VarChar).Value = line.AccountNumber;
            myCommand.Parameters.Add("@bank", OleDbType.VarChar).Value = line.Bank;
            myCommand.Parameters.Add("@sold", OleDbType.VarChar).Value = line.Sold;

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int deleteAccount(Account line)
        {
            int _return = -1;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from ACCOUNTS where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }
        #endregion
    }
}
